package com.cims.dao.land;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import com.cims.common.exception.DaoException;
import com.cims.common.vo.Grave;
import com.cims.common.vo.GravePurchased;
import com.cims.common.vo.Land;
import com.cims.common.vo.OrderItem;
import com.cims.dao.hibernate.HibernateUtil;

public class LandDAOImpl implements LandDAO {

	@Override
	public Land findLandById(int landId) {
		return HibernateUtil.findByID(Land.class, landId);
	}

	@Override
	public List<OrderItem> getListLandOfCustomer(int customerId)
			throws DaoException {
		List<OrderItem> lstVo = null;
		SessionFactory factory = HibernateUtil.getSessionFactory();
		Session session = null;

		try {
			session = factory.openSession();

			Query query = session
					.createQuery("from OrderItem as O where O.customer.customerId = :customerId and (O.item.itemId in (select G.item.itemId from Land as G))");
			query.setParameter("customerId", customerId);
			lstVo = query.list();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}

		return lstVo;
	}

	@Override
	public boolean assignGraveToLand(OrderItem o, GravePurchased graveP)
			throws DaoException {
		SessionFactory factory = HibernateUtil.getSessionFactory();
		Session session = null;

		try {
			// Note that: LandId here is ItemId, not LandId ************* =>
			// Need to get LandID from ItemID

			session = factory.openSession();
			Transaction tx = session.beginTransaction();

			SQLQuery query = session
					.createSQLQuery("insert into GravePurchased(LandId, LastName, FirstName, Hometown) values('"
							+ GetLandIdFromItemId(graveP.getLand().getLandId())
							+ "', '"
							+ graveP.getLastname()
							+ "', '"
							+ graveP.getFirstname()
							+ "', '"
							+ graveP.getHometown() + "')");
			query.executeUpdate();

			tx.commit();

			// Update Order Table to set this grave is processed
			SetProcessedGrave(o.getOrderId());

			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}

		return false;
	}

	private int GetLandIdFromItemId(int itemId) {
		SessionFactory factory = HibernateUtil.getSessionFactory();
		Session session = null;

		try {
			// Note that: LandId here is ItemId, not LandId ************* =>
			// Need to get LandID from ItemID

			session = factory.openSession();
			Transaction tx = session.beginTransaction();

			String sql = "select * from Land where ItemId = " + itemId;
			SQLQuery query0 = session.createSQLQuery(sql);
			query0.addEntity(Land.class);
			List<Land> landResults = query0.list();

			tx.commit();
			return landResults.get(0).getLandId();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}

		return -1;

	}

	private boolean SetProcessedGrave(int orderId) {
		SessionFactory factory = HibernateUtil.getSessionFactory();
		Session session = null;

		try {

			session = factory.openSession();
			Transaction tx = session.beginTransaction();

			SQLQuery query2 = session
					.createSQLQuery("Update OrderItem set GravePurchasedId = '4' where OrderItem.OrderId = "
							+ orderId);
			query2.executeUpdate();

			tx.commit();
			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}

		return false;

	}

	@Override
	public List<GravePurchased> getListGravePurchasedOfCustomer(int customerId)
			throws DaoException {
		List<GravePurchased> lstVo = null;
		SessionFactory factory = HibernateUtil.getSessionFactory();
		Session session = null;

		try {
			// Note that: LandId here is ItemId, not LandId ************* =>
			// Need to get LandID from ItemID

			session = factory.openSession();
			Transaction tx = session.beginTransaction();

			String sql = "select distinct G.* from Land as L, OrderItem as O, Item as I, GravePurchased as G where L.ItemId = I.ItemId and O.ItemId = I.ItemId and O.CustomerId = "+ customerId + " and G.LandId = L.LandId";
		
			SQLQuery query0 = session.createSQLQuery(sql);
			query0.addEntity(GravePurchased.class);
			lstVo = query0.list();

			tx.commit();
			return lstVo;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}

		return null;
	}
}
